package com.elitel.frame.base.service.impl;

import com.elitel.common.DateUtilFormat;
import com.elitel.common.MyBatisSqlUtils;
import com.elitel.common.exception.BaseException;
import com.elitel.frame.base.component.RedisCacheProjectService;
import com.elitel.frame.base.service.AcServiceFilterFieldService;
import com.elitel.frame.base.service.CfgExtsqlLogService;
import com.elitel.frame.base.service.DBServerService;
import com.elitel.frame.base.service.strategy.sql.OgnlParseSQLStrategy;
import com.elitel.frame.base.service.strategy.sql.ParseSQLStrategy;
import com.elitel.frame.main.dao.*;
import com.elitel.frame.main.dao.ext.*;
import com.elitel.frame.main.entity.*;
import com.elitel.frame.main.entity.ext.*;
import com.elitel.frame.main.entity.vo.AcServiceFilterFieldFormVo;
import com.elitel.frame.main.entity.vo.PageResponseVo;
import com.elitel.frame.main.entity.vo.ServerTree;
import com.google.gson.Gson;
import freemarker.cache.StringTemplateLoader;
import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.io.IOException;
import java.io.StringWriter;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Service
public class DBServerServiceImpl implements DBServerService {

    @Autowired
    private DBServerMapper dbServerMapper;

    @Resource
    private ExtCfgTableMapper extCfgTableMapper;

    @Resource
    private CfgTableMapper cfgTableMapper;

    @Resource
    private BaseSqlconfigMapper baseSqlconfigMapper;

    @Resource
    private CfgFieldMapper cfgFieldMapper;

    @Resource
    private CfgDbMapper cfgDbMapper;

    @Resource
    private CfgExtsqlMapper cfgExtsqlMapper;

    @Resource
    private ExtBaseQueryfieldMapper extBaseQueryfieldMapper;

    @Resource
    private BaseQueryfieldMapper baseQueryfieldMapper;

    @Resource
    private ExtBaseQuerysqlMapper extBaseQuerysqlMapper;

    @Resource
    private SysPrjserviceMapper sysPrjserviceMapper;
    @Resource
    private ExtSysPrjserviceMapper extSysPrjserviceMapper;

    @Autowired
    private RedisCacheProjectService redisCacheProjectService;

    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    @Resource
    private ExtSysExceptionMapper extSysExceptionMapper;

    @Resource
    private ExtCfgDbMapper extCfgDbMapper;

    @Resource
    private AcServiceFilterFieldService acServiceFilterFieldService;

    @Resource
    private CfgExtsqlLogService cfgExtsqlLogService;

    //	@Resource(name = "serverQueueDestination")
    //    private Destination destination;

    @Override
    public String insertServer(DBServer server) {
        String message = "fail";
        try {
            Date date = new Date();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String dateNow = sdf.format(date);
            server.setCreateDate(dateNow);
            if (server.getUser() == null) {
                server.setUser("admin");
            }
            int a = dbServerMapper.insertServer(server);
            if (a > 0) {
                message = "success";
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return message;
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public String updateServer(DBServer server) {
        String message = "fail";
        //DBServer dbServer = dbServerMapper.selectSerOne(server.getGuid());
        baseSqlconfigMapper.deleteByServerId(server.getGuid());
        extBaseQueryfieldMapper.deleteQueryfieldByServiceId(server.getGuid());

        int a = dbServerMapper.updateServer(server);
        if (a > 0) {
            message = "success";
        }

        insertLog(server);

        // 2024.01.23 基础信息添加 '是否支持数据访问限制' 功能
        if (server instanceof DBServerExt) {
            DBServerExt serverExt = (DBServerExt) server;
            AcServiceFilterFieldFormVo fieldFormVo = serverExt.getFieldFormVo();
            acServiceFilterFieldService.insert(fieldFormVo);
            // 先只更新服务标签
            SysPrjservice sysPrjservice = new SysPrjservice();
            sysPrjservice.setServicecode(serverExt.getGuid());
            sysPrjservice.setSysDataLabelId(serverExt.getSysDataLabelId());
            sysPrjservice.setCacheDur(serverExt.getCacheDur());
            sysPrjservice.setApiPath(serverExt.getApiPath());
            sysPrjservice.setResCvsJs(serverExt.getResCvsJs());
            sysPrjservice.setTableCodes(serverExt.getTableCodes());
            sysPrjservice.setServicename(serverExt.getName());
            extSysPrjserviceMapper.updateServer(sysPrjservice);
        }

        return message;
    }

    private void insertLog(DBServer server) {
        CfgExtsqlLog cfgExtsqlLog = new CfgExtsqlLog();
        cfgExtsqlLog.setServicecode(server.getGuid());
        cfgExtsqlLog.setServicename(server.getName());
        cfgExtsqlLog.setDtSql(server.getSql());
        cfgExtsqlLog.setDtDbKey(server.getDbKey());
        this.cfgExtsqlLogService.insert(cfgExtsqlLog);
    }

    /**
     * 解析 SQL 中的查询字段
     *
     * @param server
     */
    public List<BaseQueryfield> parseSQLQueryField(DBServer server) {
        int paramtype = server.getParamtype();
//        ParseSQLStrategy parseSQLStrategy = ParseSQLFactory.getStrategyNoNull(paramtype);
        if (paramtype == 0) {
            //freemark to mybatis
            String sql = server.getSql();
            String newSql = free2mybatis(sql);
            server.setSql(newSql);
        }
        ParseSQLStrategy parseSQLStrategy2 = new OgnlParseSQLStrategy();
        return parseSQLStrategy2.parseSQLQueryField(server);
    }

    public static String free2mybatis(String templateContent) {
        Map<String, String> map = new HashMap<>();
        String regex = "<#if\\s+(\\w+)\\s+\\?\\?\\s+\\&\\&\\s+\\1\\s+!=\\s*\'.*?\'>";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(templateContent);
        while (matcher.find()) {
            String pp = matcher.group();
            String replace = pp.replace("??", " != null ")
                    .replace("<#if", "")
                    .replace(">", "");
            if (pp.contains("&&")) replace = replace.replace("&&", " and ");
            map.put(pp, "<if test=\"" + replace + "\">");
        }
        String sql = templateContent;
        for (Map.Entry<String, String> entry : map.entrySet()) {
            sql = sql.replace(entry.getKey(), entry.getValue());
        }
        String tr = sql.replace("</#if>", "</if>")
                .replace("'${", "#{")
                .replace("}'", "}");
        System.out.println(tr);
        return tr;
    }

    @Override
    public List<ServerTree> selectDbName() {
        List<ServerTree> lists = new ArrayList<ServerTree>();
        try {
            List<String> list = dbServerMapper.selectDbName();
            for (String a : list) {
                ServerTree serverTree = new ServerTree();
                serverTree.setId(a);
                serverTree.setName(a);
                lists.add(serverTree);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return lists;
    }

    @Override
    public List<DBServer> selectServer(String dbKey) {
        List<DBServer> serverList = null;
        try {
            serverList = dbServerMapper.selectServers(dbKey);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return serverList;
    }

    @Override
    public String deleteServer(String id) {
        String message = "fail";
        try {
            int a = dbServerMapper.delectServer(id);
            if (a > 0) {
                baseSqlconfigMapper.deleteByServerId(id);
                extBaseQueryfieldMapper.deleteQueryfieldByServiceId(id);
                message = "success";
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return message;
    }

    @Override
    public PageResponseVo<DBServer> selectFenye(String dbKey, String pageNo, String pageSize) {
        int a = Integer.valueOf(pageNo) - 1;
        int b = Integer.valueOf(pageSize);
        int rowIndex = a * b + 1;
        int rowEnd = Integer.valueOf(pageNo) * Integer.valueOf(pageSize);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("rowEnd", rowEnd);
        map.put("dbKey", dbKey);
        map.put("rowStart", rowIndex);
        List<DBServer> list = dbServerMapper.selectFenye(map);
        for (DBServer dbServer : list) {
            if (dbServer.getCatched() == 0) {
                dbServer.setWhether("否");
            } else {
                dbServer.setWhether("是");
            }
        }
        int rowNum = dbServerMapper.selectCount(dbKey);
        PageResponseVo<DBServer> pageResponseVo = new PageResponseVo<DBServer>();
        pageResponseVo.setRows(list);
        pageResponseVo.setTotal(rowNum);
        return pageResponseVo;
    }

    @Override
    public DBServer selectServerOne(String guid) {
        DBServer server = dbServerMapper.selectSerOne(guid);
        return server;
    }

    @Override
    public Map<String, Object> selectTableName(String conn) {
        Map<String, Object> map = new HashMap<String, Object>();
        return map;
    }

    @Override
    public DBServer selectServerOnes(String conn) {
        DBServer cfgExtsql = null;
        try {
            cfgExtsql = dbServerMapper.selectServer(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return cfgExtsql;
    }

    @Override
    public Map<String, Object> selectTableMap(String conn) {
        Map<String, Object> map = new HashMap<String, Object>();
        List<CfgTable> list = cfgTableMapper.selectTableMap(conn);
        for (CfgTable c : list) {
            if (c.getDtTabletype() == 1) {
                map.put(c.getDtConfigId(), c.getDtNameCn());
            }
        }
        return map;
    }

    @Override
    public PageResponseVo<BaseSqlconfig> selectServerShintColumn(String conn, String config) {
        List<BaseSqlconfig> baseList = baseSqlconfigMapper.selectBaseByConfig(config);
        List<CfgField> list = cfgFieldMapper.selectColumnList(conn);
        List<BaseSqlconfig> sqlList = new ArrayList<BaseSqlconfig>();
        List<String> list2 = new ArrayList<>();
        for (CfgField cfgField : list) {
            for (BaseSqlconfig baseSqlConfig : baseList) {
                // if(b.getFieldcaption()==null){
                if (cfgField.getDfName().equals(baseSqlConfig.getFiledname())) {
                    BaseSqlconfig baseSqlconfig = baseSqlconfigMapper.selectByGUID(baseSqlConfig.getGuid());
                    // if (baseSqlconfig.getFieldcaption()==null) {
                    baseSqlconfig.setFieldcaption(cfgField.getDfNameCn());
                    baseSqlconfig.setDtConfigId(conn);
                    // }
                    list2.add(baseSqlconfig.getFiledname());
                    baseSqlconfig.setDtConfigId(cfgField.getDtConfigId());
                    sqlList.add(baseSqlconfig);
                    //	}
                }
            }
        }
        List<BaseSqlconfig> list3 = new CopyOnWriteArrayList<BaseSqlconfig>();
        for (BaseSqlconfig b : baseList) {
            list3.add(b);
        }
        for (BaseSqlconfig b : list3) {
            for (String xString : list2) {
                if (xString.equals(b.getFiledname())) {
                    list3.remove(b);
                }
            }
        }
        for (BaseSqlconfig b : list3) {
            sqlList.add(b);
        }
        Map<Integer, BaseSqlconfig> map = new HashMap<>();
        for (BaseSqlconfig b : sqlList) {
            map.put(b.getShoworder(), b);
        }
        List<BaseSqlconfig> list4 = new ArrayList<>();
        Map<Integer, BaseSqlconfig> map2 = new TreeMap<Integer, BaseSqlconfig>();
        for (Map.Entry<Integer, BaseSqlconfig> entry : map.entrySet()) {
            map2.put(entry.getKey(), entry.getValue());
        }
        for (Map.Entry<Integer, BaseSqlconfig> entry : map2.entrySet()) {
            list4.add(entry.getValue());
        }
        PageResponseVo<BaseSqlconfig> pageResponseVo = new PageResponseVo<BaseSqlconfig>();
        Map<String, Object> map3 = new HashMap<>();
        //		for(BaseSqlconfig  b:list4){
        //			map3.put("conn", conn);
        //			map3.put("filedname", b.getFiledname());
        //			String type = cfgFieldMapper.selectColumnType(map3);
        //			if("string".equals(type)){
        //				b.setShowtype(Integer.valueOf(2).shortValue());
        //			}else if ("date".equals(type)) {
        //				b.setShowtype(Integer.valueOf(1).shortValue());
        //			}else {
        //				b.setShowtype(Integer.valueOf(2).shortValue());
        //			}
        //		}
        for (BaseSqlconfig baseSqlconfigs : list4) {
            if (baseSqlconfigs.getFieldcaption() == null || "".equals(baseSqlconfigs.getFieldcaption())) {
                baseSqlconfigs.setFieldcaption(baseSqlconfigs.getFiledname());
            }
        }

        pageResponseVo.setRows(list4);
        pageResponseVo.setTotal(sqlList.size());
        return pageResponseVo;
    }

    //	 /**
    //     * 使用 Map按key进行排序
    //     * @param map
    //     * @return
    //     */
    //    public static Map<Integer, BaseSqlconfig> sortMapByKey(Map<Integer, BaseSqlconfig> map) {
    //        if (map == null || map.isEmpty()) {
    //            return null;
    //        }
    //
    //        Map<Integer, BaseSqlconfig> sortMap = new TreeMap<Integer, BaseSqlconfig>(
    //                new MapKeyComparator());
    //
    //        sortMap.putAll(map);
    //
    //        return sortMap;
    //    }

    @Override
    public String saveBaseSqlConfig(List<BaseSqlconfig> baseSqlconfig) {
        String message = "fail";
        int a = 0;
        for (BaseSqlconfig b : baseSqlconfig) {
            a = baseSqlconfigMapper.updateByPrimaryKeySelective(b);
        }
        if (a > 0) {
            message = "success";
        }
        return message;
    }

    @Override
    public List<Census> selectField() {
        List<Census> censusList = new ArrayList<Census>();
        List<CfgDb> list = cfgDbMapper.selectAllDb();
        for (CfgDb c : list) {
            Census census = new Census();
            census.setId(c.getDbKey());
            census.setName(c.getDbMark() + "(" + c.getDbKey() + ")");
            census.setPid(String.valueOf(0));
            int a = cfgExtsqlMapper.selectSql(c.getDbKey());
            if (a > 0) {
                List<CfgExtsql> list2 = cfgExtsqlMapper.selectByDbKey(c.getDbKey());
                List<Census> list3 = new ArrayList<Census>();
                for (CfgExtsql cg : list2) {
                    Census cen = new Census();
                    cen.setId(cg.getGuid());
                    cen.setName(cg.getServicename());
                    cen.setPid(cg.getDtDbKey());
                    list3.add(cen);
                }
                census.setChildren(list3);
            }
            censusList.add(census);
        }
        return censusList;
    }

    @Override
    public List<BaseSqlconfig> selectServerColumn(String serverid) {
        List<BaseSqlconfig> list = null;
        String sql = "";
        Map<String, Object> columnMap = new HashMap<>();
        try {
            list = baseSqlconfigMapper.selectServerColumn(serverid);
            sql =
                    sqlSessionFactory
                            .getConfiguration()
                            .getMappedStatement(
                                    "com.elitel.frame.main.dao.BaseSqlconfigMapper.selectServerColumn")
                            .getBoundSql(serverid)
                            .getSql();
            columnMap.put(sql, list);
            //			if (columnMap.size()>0) {
            //				ActiveMQUtil.saveActivemqString(jmsTemplate, destination, uuid, columnMap.toString());
            //			}
        } catch (Exception e) {
            throw new BaseException(e, sql, getSysException());
        }
        return list;
    }

    /**
     * <pre>processSqlByFreemarker(freemarker处理sql)
     * @author：FanWenquan
     * @date：2017年8月31日 上午10:34:17
     * @param sql
     * @param root
     * @return</pre>
     */
    private String processSqlByFreemarker(String sql, Map root) {
        String str = "";
        Configuration cfg = new Configuration();
        StringTemplateLoader stringLoader = new StringTemplateLoader();
        stringLoader.putTemplate("myTemplate", sql);
        cfg.setTemplateLoader(stringLoader);
        try {
            Template template = cfg.getTemplate("myTemplate", "utf-8");
            StringWriter writer = new StringWriter();
            template.process(root, writer);
            str = writer.toString();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (TemplateException e) {
            e.printStackTrace();
        }
        return str;
    }

    @Override
    public List<DBServer> selectALLsql() {
        List<DBServer> list = dbServerMapper.selectALLsql();
        return list;
    }

    @Override
    public List<BaseSqlconfig> selectServerColumns(String serverid) {

        List<BaseSqlconfig> list = baseSqlconfigMapper.selectServerColumns(serverid);
        return list;
    }

    @Override
    public List<CfgExtsql> getExtSqlList(String dbkey, String searchkey) {
        List<CfgExtsql> list = null;
        if ("".equals(searchkey)) {
            list = cfgExtsqlMapper.getExtSqlList(dbkey);
        } else {
            Map<String, Object> map = new HashMap<>();
            map.put("dbkey", dbkey);
            map.put("searchContent", searchkey);
            list = cfgExtsqlMapper.getExtSqlListMap(map);
        }
        return list;
    }

    @Override
    public CfgDb getCfgDbOne(String dbkey) {
        CfgDb cfgDb = cfgDbMapper.selectByPrimaryKey(dbkey);
        return cfgDb;
    }

    @Override
    public String insertField(BaseQueryfield baseQueryfield) {
        String message = "fail";
        baseQueryfield.setGuid(UUID.randomUUID().toString());
        int a = baseQueryfieldMapper.insertSelective(baseQueryfield);
        if (a > 0) {
            message = "success";
        }
        return message;
    }

    @Override
    public List<BaseQueryfield> selectAllFiled(String serviceID) {
        List<BaseQueryfield> list = extBaseQueryfieldMapper.selectAllField(serviceID);
        return list;
    }

    @Override
    public String saveFiledConfig(List<BaseQueryfield> baseQueryfields) {
        String message = "fail";
        int a = baseQueryfields.size();
        int b = 0;
        for (BaseQueryfield bQueryfield : baseQueryfields) {
            b += baseQueryfieldMapper.updateByPrimaryKeySelective(bQueryfield);
        }
        if (b == a) {
            message = "success";
        }
        return message;
    }

    @Override
    public String deleteFiled(String guid) {
        String message = "fail";
        int a = baseQueryfieldMapper.deleteByPrimaryKey(guid);
        if (a > 0) {
            message = "success";
        }
        return message;
    }

    @Override
    public Map<String, String> selectAllTableMap(String conn) {
        List<CfgTable> list = extCfgTableMapper.getTableListBydbkey(conn);
        Map<String, String> map = new HashMap<>();
        for (CfgTable cfgTable : list) {
            map.put(cfgTable.getDtConfigId(), cfgTable.getDtName() + "(" + cfgTable.getDtNameCn() + ")");
        }
        return map;
    }

    @Override
    public List<QueryFiled> selectServerField(String guid) {
        Gson gson = new Gson();
        Map<String, Object> parameterMap = new HashMap<>();
        Map<String, Object> columnMap = new HashMap<>();
        List<DebugTestVO> debugTestVOList = new ArrayList<>();
        String sql = "";
        List<QueryFiled> list2 = new ArrayList<>();
        try {
            List<BaseQueryfield> list = extBaseQueryfieldMapper.selectAllField(guid);
            parameterMap.put("serviceID", guid);
            MyBatisSql myBatisSql =
                    MyBatisSqlUtils.getMyBatisSql(
                            "com.elitel.frame.main.dao.ext.ExtBaseQueryfieldMapper.selectAllField",
                            parameterMap,
                            sqlSessionFactory);
            debugTestVOList.add(
                    new DebugTestVO(
                            gson.toJson(list), myBatisSql.getSql(), DateUtilFormat.dataToString(new Date())));
            columnMap.put(myBatisSql.getSql(), list);
            for (BaseQueryfield baseQueryfield : list) {
                QueryFiled queryFiled = new QueryFiled();
                queryFiled.setGuid(baseQueryfield.getGuid());
                queryFiled.setParametercode(baseQueryfield.getParametercode());
                queryFiled.setParametervalue(baseQueryfield.getParametervalue());
                queryFiled.setRemark(baseQueryfield.getRemark());
                queryFiled.setServiceid(baseQueryfield.getServiceid());
                queryFiled.setServicetype(String.valueOf(baseQueryfield.getServicetype()));
                queryFiled.setShowtype(String.valueOf(baseQueryfield.getShowtype()));
                queryFiled.setFieldJavaType(Optional.ofNullable(baseQueryfield.getParamformat()).orElse("String"));
                list2.add(queryFiled);
            }
        } catch (Exception e) {
            throw new BaseException(e, sql, getSysException());
        }
        return list2;
    }

    @Override
    public BaseQuerysql selectQuerySql(String guid, int grade) {
        Map<String, Object> map = new HashMap<>();
        map.put("guid", guid);
        map.put("grade", grade);
        BaseQuerysql baseQuerysql = extBaseQuerysqlMapper.selectQuerySql(map);
        // BaseQuerysql baseQuerysql = null;
        return baseQuerysql;
    }

    @Override
    public Integer insertSystem(SysPrjservice sysPrjservice) {
        int a = sysPrjserviceMapper.insertSelective(sysPrjservice);
        return a;
    }

    @Override
    public void deleteexecuteSqlServerByPrjservice(SysPrjservice prjservice) throws Exception {
        // 删除服务中间表信息 转换
        sysPrjserviceMapper.deleteByPrimaryKey(prjservice.getGuid());
        dbServerMapper.delectServer(prjservice.getServicecode());
        baseSqlconfigMapper.deleteByServerId(prjservice.getServicecode());
        extBaseQueryfieldMapper.deleteQueryfieldByServiceId(prjservice.getServicecode());
        // 刷新redis缓存
        redisCacheProjectService.freshProjectServerTree();
    }

    public Map<String, SysException> getSysException() {
        List<Map<String, Object>> lists = null;
        Map<String, SysException> map = new HashMap<>();
        List<SysException> list = extSysExceptionMapper.selectAllList();
        for (SysException sysException : list) {
            map.put(sysException.getExceptionName(), sysException);
        }
        return map;
    }

    @Override
    public String selectDbType(String dbkey) {
        CfgDb cfgDb = cfgDbMapper.selectByPrimaryKey(dbkey);
        return cfgDb.getDbVersion();
    }
}
